
// correlates stickiness with moments of French data 

cd $firmpath 

*** Build market thickness 

use panel_f2f_clean_19932017, clear 
keep if year==2002
tostring nc8, replace
replace nc8="0"+nc8 if length(nc8)==7 
g hs6 =substr(nc8,1,6) 
drop if length(nc8)!=8 
egen fp=group(siren hs6)
collapse (sum) export, by(hs6 siren)
sum export, d
drop if export<1000 
collapse (count) nb_french=export, by(hs6)  
save num_french_hs6, replace 

insheet using $countrypath\baci02_2002.csv, clear

collapse (sum) v, by(k i)
drop if v<10
egen _=sum(v), by(k) 
g herf=(v/_)^2 
g __=(v/_ )*(i==251) 

collapse (sum) herf export_baci=v (count) nb_cty=v (max) mks_fra=__, by(k) 
rename k hs6 
tostring hs6, replace 
replace hs6="0"+ hs6 if length(hs6)==5
merge 1:1 hs6 using num_french_hs6
keep if _m==3 
drop _m 
g nb_firm_world=nb_french/mks_fra
save thickness_world, replace 


*** build sales dispersion 

use panel_f2f_clean_19932017, clear 
keep if year==2002
drop if export==0 | export==.
tostring nc8, replace
replace nc8="0"+nc8 if length(nc8)==7 
g hs6 =substr(nc8,1,6) 
drop if length(nc8)!=8 
collapse (sum) export , by(hs6 siren) 
egen _=sum(export), by(hs6) 
g hhi=(export/_)*(export/_)
collapse (sum) hhi (iqr) iqr_sale=export (count) nbf=export, by(hs6) 
save iqr_sale, replace 


*** build price dispersion 

use panel_f2f_clean_19932017, clear 
keep if year==2002
drop if export==0 | export==.
tostring nc8, replace
replace nc8="0"+nc8 if length(nc8)==7 
g hs6 =substr(nc8,1,6) 
drop if length(nc8)!=8 
rename unites usup 
rename kgs masse
drop if usup==0 & masse==0
drop if usup==0 & masse==.
drop if usup==. & masse==.
drop if usup==. & masse==0 
drop if export==0 | export==.
collapse (sum) export masse usup, by(iso2 nc8 hs6 siren buyer) 
g uv=export/usup
replace uv=export/masse if uv==. 
egen median=median(uv), by(hs6)
g dev=uv/median 
drop if dev>100 | dev<0.01

collapse (sd) sd_uv=uv (mean) m_uv=uv (count) nbuyer=uv, by(siren hs6 nc8)
drop if nbuyer<3
g lcv=log(sd_uv/m_uv)
g ln=log(nbuyer) 
reghdfe lcv ln , a(prod=nc8)
collapse (mean) dispersion_f=prod cv_f=lcv , by(hs6)
save cv_uv_f, replace 

*** build share of wholesalers 

/* code run on FICUS FARE data 
clear
set obs 1
g year=2002
drop if year==2002
save temp, replace
global brnpath xxxx\BRN19902008
global product id_conc 
local y 2002
	use "$brnpath/brn`y'", clear
	drop if siren==""
	drop if siren=="000000000"
	g WS=0
	replace WS=1 if substr(ape,1,2)=="51"
	duplicates drop siren year, force
	keep siren WS year
	append using temp
	save temp, replace
	use "$brnpath/rsi`y'", clear
	drop if siren==""
	drop if siren=="000000000"
	g WS=0
	replace WS=1 if substr(ape,1,2)=="51"
	duplicates drop siren, force
	keep siren WS year
	append using temp
	duplicates drop siren year, force
	save temp, replace
	
use panel_f2f_clean_19932017, clear 
keep if year==2002
drop if export==0 | export==.
tostring nc8, replace
replace nc8="0"+nc8 if length(nc8)==7 
g hs6 =substr(nc8,1,6) 
drop if length(nc8)!=8 
merge m:1 siren using temp
keep if _merge==3
g exportWS=export*WS
bys hs6: egen count_s=nvals(siren)
bys hs6: egen count_WSs=nvals(siren) if WS==1
collapse (mean) count_s count_WSs (sum) export exportWS , by(hs6)
g exportShare_WS=exportWS/export
g countShare_WS=count_WSs/count_s
sort hs6
save share_wholesale, replace 
erase temp.dta
*/ 

*** analysis 

use  $rspath/rs_hs02_19962006 , clear
g hs6=hs6_2002
merge 1:1 hs6 using thickness_world
keep if _m==3
drop _m
merge 1:1 hs6 using iqr_sale
drop _m
merge 1:1 hs6 using  cv_uv_f 
drop _m
sort hs6 
merge 1:1 hs6 using  sharesalesman_by_hs6.dta  // share of sales men  
drop if _m==2  
drop _m
merge 1:1 hs6 using  KoY_hs6
drop _m
merge 1:1 hs6 using  share_wholesale 

foreach i in KoY_Ymed_sect med_KoY_Ymed_sect KoY_Ymed_mean KoY_Ymed_med{
	g l`i'=log(`i')
}

g lnbc=log(nb_cty)
g lnb_wld=log(nb_firm_world)
g lnb_fr=log(nb_french)

putexcel set $outputpath\table4.xls, replace
putexcel set $outputpath\table4.xls, replace sheet("results")

putexcel B1="Proxy for market thickness"

putexcel B2="# of firms worldwide"
local i lnb_wld 
reg rs_hs6_b `i'
putexcel C2=_b[`i']
putexcel D2=_se[`i']
putexcel E2=(e(r2)) 

putexcel B3="# of exporting countries"
local i lnbc 
reg rs_hs6_b `i'
putexcel C3=_b[`i']
putexcel D3=_se[`i']
putexcel E3=(e(r2)) 

putexcel B4="# of French exporters"
local i lnb_fr  
reg rs_hs6_b `i'
putexcel C4=_b[`i']
putexcel D4=_se[`i']
putexcel E4=(e(r2))

putexcel B5="French HHI"
local i hhi  
reg rs_hs6_b `i'
putexcel C5=_b[`i']
putexcel D5=_se[`i']
putexcel E5=(e(r2)) 

putexcel B6="Proxy for search frictions"

putexcel B7="Share salesmen"
local i share_salesman_nball  
reg rs_hs6_b `i'
putexcel C7=_b[`i']
putexcel D7=_se[`i']
putexcel E7=(e(r2)) 

putexcel B8="Wage bill salesmen"
local i share_salesman_billall  
reg rs_hs6_b `i'
putexcel C8=_b[`i']
putexcel D8=_se[`i']
putexcel E8=(e(r2)) 

putexcel B9="Price dispersion"
local i dispersion_f  
reg rs_hs6_b `i'
putexcel C9=_b[`i']
putexcel D9=_se[`i']
putexcel E9=(e(r2))

putexcel B10="All market. det."
reg rs_hs6_b lnb_wld lnb_fr lnbc hhi dispersion_f cv_f share_salesman_nball share_salesman_billall 
putexcel E10=(e(r2)) 


putexcel B11="Proxy for technological specificity"

putexcel B13="Sunk costs (med)"
local i lKoY_Ymed_med
reg rs_hs6_b `i'  
putexcel C13=_b[`i']
putexcel D13=_se[`i']
putexcel E13=(e(r2))

putexcel B14="Sh. wholesale (value)"
local i exportShare_WS
reg rs_hs6_b `i'  
putexcel C14=_b[`i']
putexcel D14=_se[`i']
putexcel E14=(e(r2))

putexcel B15="Sh. wholesale (count)"
local i countShare_WS
reg rs_hs6_b `i'  
putexcel C15=_b[`i']
putexcel D15=_se[`i']
putexcel E15=(e(r2))

putexcel B16="All techno. det."
reg rs_hs6_b lKoY_Ymed_med exportShare_WS countShare_WS  
putexcel E16=(e(r2)) 

 
use $rspath/rs_baseline_id_conc19962006, clear
duplicates drop id_conc, force
merge m:1 id_conc using share_onetransactionhistories_by$product
keep if _m==3
drop _m
merge m:1 id_conc using multisupplier_by$product
replace sh_multi=0 if _m==1
replace shv_multi=0 if _m==1
pwcorr rs_hs6_b share_onetransaction vshare_onetransaction   // -.21 -.11
pwcorr rs_hs6_b shv_multi sh_multi   // -.26 -.35


